package ucsd.ties.dvs;

import java.io.File;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.text.DecimalFormat;
import java.util.Locale;
import java.util.Scanner;
import jxl.Cell;
import jxl.CellView;
import jxl.Workbook;
import jxl.WorkbookSettings;
import jxl.read.biff.BiffException;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;

public class WriteToExcel {
	@SuppressWarnings("unused")
	private static final WritableSheet WritableSheet = null;
	private WritableCellFormat arialBold;
	private WritableCellFormat arial;

	private String inputFile;
	private String receiptNumField;
	private String reportNameField;
	private String dataField;
	private String locationField;
	private String screenerField;
	private String birthdayField;
	private String regionField;
	private String zipField;
	private String schoolIDField;
	private String organizationField;
	private String locField;
	private String textfile;
	private String ageField;
	private String genderField;
	private String ethnicityField;
	private String roomField;
	private String schoolYearField;;
	private String languageField;
	private String commentField;

	// // Left eye data
	// private String col20;
	// private String col21;
	// private String col22;
	//
	// // Right eye data
	// private String col24;
	// private String col25;
	// private String col26;
	//
	// // These are set up for clear function to clear the pre-loaded data
	// private String col0;
	// private String col9;
	// private String col19;
	// private String col23;

	private boolean fileExist = false;

	public WriteToExcel(String inputFile, String receiptNumField,
			String reportNameField, String dateField, String locationField,
			String screenerField, String dOBField, String regionField,
			String zipField, String schoolIDField, String organizationField,
			String locField, String ageField, String genderField,
			String ethnicityField, String roomField, String schoolYearField,
			String languageField, String commentField, String textFile) {
		super();
		this.inputFile = inputFile;
		this.receiptNumField = receiptNumField;
		this.reportNameField = reportNameField;
		this.dataField = dateField;
		this.locationField = locationField;
		this.screenerField = screenerField;
		this.birthdayField = dOBField;
		this.regionField = regionField;
		this.zipField = zipField;
		this.schoolIDField = schoolIDField;
		this.organizationField = organizationField;
		this.locField = locField;
		this.textfile = textFile;
		this.ageField = ageField;
		this.genderField = genderField;
		this.ethnicityField = ethnicityField;
		this.roomField = roomField;
		this.schoolYearField = schoolYearField;
		this.languageField = languageField;
		this.commentField = commentField;
	}

	public WriteToExcel(String inputFile, String receiptNumField,
			String reportNameField, String dateField, String locationField,
			String screenerField, String dOBField, String regionField,
			String zipField, String schoolIDField, String organizationField,
			String locField, String ageField, String genderField,
			String ethnicityField, String roomField, String schoolYearField,
			String languageField, String commentField, String col0,
			String col9, String col19, String col20, String col21,
			String col22, String col23, String col24, String col25, String col26) {
		super();
		this.inputFile = inputFile;
		this.receiptNumField = receiptNumField;
		this.reportNameField = reportNameField;
		this.dataField = dateField;
		this.locationField = locationField;
		this.screenerField = screenerField;
		this.birthdayField = dOBField;
		this.regionField = regionField;
		this.zipField = zipField;
		this.schoolIDField = schoolIDField;
		this.organizationField = organizationField;
		this.locField = locField;
		this.ageField = ageField;
		this.genderField = genderField;
		this.ethnicityField = ethnicityField;
		this.roomField = roomField;
		this.schoolYearField = schoolYearField;
		this.languageField = languageField;
		this.commentField = commentField;
		// this.col0 = "";
		// this.col9 = "";
		// this.col19 = "";
		// this.col20 = "";
		// this.col21 = "";
		// this.col22 = "";
		// this.col23 = "";
		// this.col24 = "";
		// this.col25 = "";
		// this.col26 = "";
	}

	// Replace with left eye
	public WriteToExcel(String col20, String col21, String col22) {

	}

	public void write() throws IOException, WriteException, BiffException {
		WorkbookSettings wbSettings = new WorkbookSettings();
		wbSettings.setLocale(new Locale("en", "EN"));
		WritableWorkbook workbook;
		WritableSheet excelSheet;

		if (CreatePanel.getIsCreateNewFile()) {
			File file = new File(inputFile);
			workbook = Workbook.createWorkbook(file, wbSettings);

			workbook.createSheet("Vision", 0);
			excelSheet = workbook.getSheet(0);
		} else {
			Workbook wb = Workbook.getWorkbook(new File(inputFile));
			workbook = Workbook.createWorkbook(new File(inputFile), wb);
			excelSheet = workbook.getSheet(0);
		}

		if (CreatePanel.getIsCreateNewFile()) {
			createLable(excelSheet);
			addContent(excelSheet);
		}

		addPatient(excelSheet, receiptNumField);
		workbook.write();
		workbook.close();
	}

	public void delete() throws IOException, WriteException, BiffException {
		WorkbookSettings wbSettings = new WorkbookSettings();
		wbSettings.setLocale(new Locale("en", "EN"));
		WritableWorkbook deleteWorkbook;
		WritableSheet excelSheet;

		if (CreatePanel.getIsCreateNewFile()) {
			File file = new File(inputFile);
			deleteWorkbook = Workbook.createWorkbook(file, wbSettings);

			deleteWorkbook.createSheet("Vision", 0);
			excelSheet = deleteWorkbook.getSheet(0);
		} else {
			Workbook wb = Workbook.getWorkbook(new File(inputFile));
			deleteWorkbook = Workbook.createWorkbook(new File(inputFile), wb);
			excelSheet = deleteWorkbook.getSheet(0);
		}

		if (CreatePanel.getIsCreateNewFile()) {
			createLable(excelSheet);
			addContent(excelSheet);
		}

		deletePatient(excelSheet, receiptNumField);
		deleteWorkbook.write();
		deleteWorkbook.close();
	}

	private void addPatient(WritableSheet sheet, String receiptNumField)
			throws WriteException {
		int row = receiptNumLookUp(sheet, receiptNumField);

		if (row >= 0) {
			// since it found the matched receipt #, it start with the name
			addData(sheet, 1, row, reportNameField);
			addData(sheet, 2, row, dataField);
			addData(sheet, 3, row, locationField);
			addData(sheet, 4, row, screenerField);
			addData(sheet, 5, row, birthdayField);
			addData(sheet, 6, row, ageField);
			addData(sheet, 7, row, genderField);
			addData(sheet, 8, row, ethnicityField);
			addData(sheet, 10, row, roomField);
			addData(sheet, 11, row, commentField);
			addData(sheet, 12, row, regionField);
			addData(sheet, 13, row, zipField);
			addData(sheet, 14, row, schoolIDField);
			addData(sheet, 15, row, schoolYearField);
			addData(sheet, 16, row, organizationField);
			addData(sheet, 17, row, languageField);
			addData(sheet, 18, row, locField);
		}
	}

	private void deletePatient(WritableSheet sheet, String receiptNumField)
			throws WriteException {
		int row = receiptNumLookUp(sheet, receiptNumField);
		if (row >= 0)
			sheet.removeRow(row);
		/*
		 * if (row >= 0) { addData(sheet, 0, row, ""); addData(sheet, 1, row,
		 * reportNameField); addData(sheet, 2, row, dataField); addData(sheet,
		 * 3, row, locationField); addData(sheet, 4, row, screenerField);
		 * addData(sheet, 5, row, birthdayField); addData(sheet, 6, row,
		 * ageField); addData(sheet, 7, row, genderField); addData(sheet, 8,
		 * row, ethnicityField); addData(sheet, 9, row, ""); addData(sheet, 10,
		 * row, roomField); addData(sheet, 11, row, commentField);
		 * addData(sheet, 12, row, regionField); addData(sheet, 13, row,
		 * zipField); addData(sheet, 14, row, schoolIDField); addData(sheet, 15,
		 * row, schoolYearField); addData(sheet, 16, row, organizationField);
		 * addData(sheet, 17, row, languageField); addData(sheet, 18, row,
		 * locField); addData(sheet, 19, row, ""); addData(sheet, 20, row, "");
		 * addData(sheet, 21, row, ""); addData(sheet, 22, row, "");
		 * addData(sheet, 23, row, ""); addData(sheet, 24, row, "");
		 * addData(sheet, 25, row, ""); addData(sheet, 26, row, ""); }
		 */
	}

	public int receiptNumLookUp(WritableSheet sheet, String receiptNumField)
			throws WriteException {
		int row = 0;
		Cell cells[] = sheet.getColumn(0);

		for (Cell cell : cells) {
			String patientNum = cell.getContents();
			System.out.println(" patientNum " + patientNum + " row\t" + row
					+ " receiptNumField\t" + receiptNumField);
			if (patientNum.equals(receiptNumField)) {
				return row;
			}
			row++;
		}
		return -1;
	}

	public int eyeDataLookUp(WritableSheet sheet, String column20,
			String column21, String column22) throws WriteException {
		@SuppressWarnings("unused")
		int row = 0;
		Cell firstCells[] = sheet.getColumn(20);

		for (@SuppressWarnings("unused")
		Cell cell : firstCells) {

		}
		return -1;
	}

	/**************************
	 * Check for duplicate row
	 **************************/

	public int getDuplicateRow(WritableSheet sheet, String name,
			String location, String dob, String zipcode, String locId)
			throws WriteException {

		Cell nameCells[] = sheet.getColumn(1);
		Cell locationCells[] = sheet.getColumn(2);
		Cell dobCells[] = sheet.getColumn(3);
		Cell zipcodeCells[] = sheet.getColumn(4);
		Cell locIdCells[] = sheet.getColumn(5);

		int row = 0;

		for (int i = 0; i < nameCells.length; i++) {
			String patientName = nameCells[i].getContents();
			String loca = locationCells[i].getContents();
			String dateOfBirth = dobCells[i].getContents();
			String zipCode = zipcodeCells[i].getContents();
			String locNum = locIdCells[i].getContents();

			if (patientName.equals(name) && loca.equals(location)
					&& dateOfBirth.equals(dob) && zipCode.equals(zipcode)
					&& locNum.equals(locId)) {

				row = i;
				continue;
			}
		}
		if (row != 0)
			return row;
		return -1;
	}

	private void createLable(WritableSheet sheet) throws WriteException {
		WritableFont arial10 = new WritableFont(WritableFont.ARIAL, 10);
		arial = new WritableCellFormat(arial10);
		WritableFont arial10ptBold = new WritableFont(WritableFont.ARIAL, 10,
				WritableFont.BOLD, false);
		arialBold = new WritableCellFormat(arial10ptBold);

		CellView cv = new CellView();
		cv.setFormat(arial);
		cv.setFormat(arialBold);
		cv.setAutosize(true);

		addCaption(sheet, 0, 0, "#");
		addCaption(sheet, 1, 0, "Report Name");
		addCaption(sheet, 2, 0, "Date");
		addCaption(sheet, 3, 0, "Location");
		addCaption(sheet, 4, 0, "Screener");
		addCaption(sheet, 5, 0, "DOB");
		addCaption(sheet, 6, 0, "Age");
		addCaption(sheet, 7, 0, "Gender");
		addCaption(sheet, 8, 0, "Ethnicity");
		addCaption(sheet, 9, 0, "Vision");
		addCaption(sheet, 10, 0, "Room");
		addCaption(sheet, 11, 0, "Screener Comment");
		addCaption(sheet, 12, 0, "Region");
		addCaption(sheet, 13, 0, "Zip");
		addCaption(sheet, 14, 0, "School ID");
		addCaption(sheet, 15, 0, "School year");
		addCaption(sheet, 16, 0, "Organization");
		addCaption(sheet, 17, 0, "Language");
		addCaption(sheet, 18, 0, "Loc. #");
		addCaption(sheet, 19, 0, "OD SPH");
		addCaption(sheet, 20, 0, "OD CYL");
		addCaption(sheet, 21, 0, "OD AXIS");
		addCaption(sheet, 22, 0, "OD R");
		addCaption(sheet, 23, 0, "OS SPH");
		addCaption(sheet, 24, 0, "OS CYL");
		addCaption(sheet, 25, 0, "OS AXIS");
		addCaption(sheet, 26, 0, "OS R");
	}

	public void deleteContent(WritableSheet sheet) throws WriteException,
			RowsExceededException, FileNotFoundException {
		int deleteRow = receiptNumLookUp(sheet, receiptNumField);
		if (deleteRow >= 0) {
			addData(sheet, 1, deleteRow, "");
			addData(sheet, 2, deleteRow, "");
			addData(sheet, 3, deleteRow, "");
			addData(sheet, 4, deleteRow, "");
			addData(sheet, 5, deleteRow, "");
			addData(sheet, 6, deleteRow, "");
			addData(sheet, 7, deleteRow, "");
			addData(sheet, 8, deleteRow, "");
			addData(sheet, 10, deleteRow, "");
			addData(sheet, 11, deleteRow, "");
			addData(sheet, 12, deleteRow, "");
			addData(sheet, 13, deleteRow, "");
			addData(sheet, 14, deleteRow, "");
			addData(sheet, 15, deleteRow, "");
			addData(sheet, 16, deleteRow, "");
			addData(sheet, 17, deleteRow, "");
			addData(sheet, 18, deleteRow, "");
			addData(sheet, 9, deleteRow, "");
			addData(sheet, 19, deleteRow, "");
			addData(sheet, 20, deleteRow, "");
			addData(sheet, 21, deleteRow, "");
			addData(sheet, 22, deleteRow, "");
			addData(sheet, 23, deleteRow, "");
			addData(sheet, 24, deleteRow, "");
			addData(sheet, 25, deleteRow, "");
			addData(sheet, 26, deleteRow, "");
		}
	}

	private void addContent(WritableSheet sheet) throws WriteException,
			RowsExceededException, FileNotFoundException {

		Scanner scan = new Scanner(new File(textfile));

		String[] row = new String[800];
		int a = 1;
		int b = 2;
		int c = 3;
		int d = 4;
		String StNo = "1";
		String StRSph = "null";
		String StRSphSign = "null";
		String StLSph = "null";
		String StLSphSign = "null";
		String StRCyl = "null";
		String StRCylSign = "null";
		String StLCyl = "null";
		String StLCylSign = "null";
		String StRAxis = "null";
		String StLAxis = "null";
		int j = 0;

		while (scan.hasNext()) {
			row[a] = scan.nextLine();
			row[b] = scan.nextLine();
			row[c] = scan.nextLine();
			row[d] = scan.nextLine();

			StNo = row[c].substring(31);
			StRSph = row[c].substring(5, 10);
			StRSphSign = row[c].substring(4, 5);
			StLSph = row[d].substring(5, 10);
			StLSphSign = row[d].substring(4, 5);
			StRCyl = row[c].substring(13, 18);
			StRCylSign = row[c].substring(12, 13);
			StLCyl = row[d].substring(13, 18);
			StLCylSign = row[d].substring(12, 13);
			StRAxis = row[c].substring(20, 23);
			StLAxis = row[d].substring(20, 23);

			a = a + 4;
			b = b + 4;
			c = c + 4;
			d = d + 4;
			j++;

			double RSphAbs = Double.parseDouble(StRSph);

			// Converts back to string, adds sign, converts to double then to
			String StRSphTemp = Double.toString(RSphAbs);
			String StRSph2 = StRSphSign + StRSphTemp;
			Double RSph = Double.parseDouble(StRSph2);

			double LSphAbs = Double.parseDouble(StLSph);

			String StLSphTemp = Double.toString(LSphAbs);
			String StLSph2 = StLSphSign + StLSphTemp;
			double LSph = Double.parseDouble(StLSph2);

			double RCylAbs = Double.parseDouble(StRCyl);

			String StRCylTemp = Double.toString(RCylAbs);
			String StRCyl2 = StRCylSign + StRCylTemp;
			double RCyl = Double.parseDouble(StRCyl2);

			double LCylAbs = Double.parseDouble(StLCyl);

			String StLCylTemp = Double.toString(LCylAbs);
			String StLCyl2 = StLCylSign + StLCylTemp;
			double LCyl = Double.parseDouble(StLCyl2);

			// double RAxis = Double.parseDouble(StRAxis);
			// double LAxis = Double.parseDouble(StLAxis);

			DecimalFormat df = new DecimalFormat("#0.00");
			// String RSphFull = df.format(Double.toString(RSph));
			addData(sheet, 0, j, StNo);
			addData(sheet, 19, j, df.format(RSph));
			addData(sheet, 20, j, df.format(RCyl));
			addData(sheet, 21, j, StRAxis);
			// addData(sheet, 22, 1, StRR); Enter Reliability from interface to
			// come
			addData(sheet, 23, j, df.format(LSph));
			addData(sheet, 24, j, df.format(LCyl));
			addData(sheet, 25, j, StLAxis);
			// addData(sheet, 26, 1, StLR); Enter Reliability from interface to
			// come

			int i = 0;

			if (RSph <= -3.5) {
				i++;
			}

			if (LSph <= -3.5) {
				i++;
			}

			if (RSph >= 1.5) {
				i++;
			}

			if (LSph >= 1.5) {
				i++;
			}

			if (RSph < 0 && LSph < 0 && Math.abs(RSph - LSph) >= 2) {
				i++;
			}
			if (RSph < 0 && LSph > 0 && Math.abs(RSph - LSph) >= 1.5) {
				i++;
			}
			if (RCyl <= -1.5) {
				i++;
			}
			if (LCyl <= -1.5) {
				i++;
			}
			if (Math.abs(RCyl - LCyl) >= 1) {
				i++;
			}
			if (i >= 1)
				addData(sheet, 9, j, "Refer");
			else {
				addData(sheet, 9, j, "Pass");
			}
		}
	}

	public void addData(WritableSheet sheet, int column, int row, String temp)
			throws RowsExceededException, WriteException {

		Label label;
		label = new Label(column, row, temp);

		if (fileExist) {
			sheet.addCell(label);
		} else {
			Label newLabel = new Label(column, row, temp);
			sheet.addCell(newLabel);
		}
	}

	public void addCaption(WritableSheet sheet, int column, int row, String s)
			throws RowsExceededException, WriteException {
		Label label;
		label = new Label(column, row, s, arialBold);

		if (CreatePanel.getIsCreateNewFile()) {
			fileExist = CreatePanel.getIsCreateNewFile();
		}
		if (fileExist) {
			sheet.addCell(label);
		} else {
			Label newLabel = new Label(column, row, s);
			sheet.addCell(newLabel);
		}
		CreatePanel.setIsCreateNewFile(false);
	}
}
